package tw.com.softleader.reins.demo.book.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.book.entity.Book;

@Repository
public class BookDao {
	
	@Autowired
	private DataSource dataSource;
	private static final String SELECT_ALL = "SELECT * FROM booktable ORDER BY id";
	private static final String SELECT_BY_ID = "SELECT * FROM booktable WHERE id = ?";
	private static final String INSERT_FULL = "INSERT INTO booktable VALUES (default,?,?,now(),?)";
//	private static final String UPDATE_BY_TITLE = "UPDATE booktable SET title=?, writer=?, publication=now(),isbn=? WHERE title=?";
//	private static final String DELETE_BY_ATTRIBUTE = "DELETE FROM booktable WHERE title = ? AND writer = ? AND isbn= ?";
	private static final String DELETE_BY_DATE_TIME = "DELETE FROM booktable WHERE publication > ? AND publication < ?";
	
	//列出所有資料
	public List<Book> findAll(){
		List<Book> result = new ArrayList<Book>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			//SQL指令 SELECT * FROM booktable ORDER BY id
			pstmt = conn.prepareStatement(SELECT_ALL);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(new Book(rs.getLong(1), rs.getString(2), rs.getString(3), rs.getTimestamp(4), rs.getString(5)));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			//依序關閉rs pstmt conn
			if( rs != null ) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return result;
	}
	
	
	//尋找指定ID的資料
	public Book findById(Long id){
		
		Book book = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			//SQL指令 SELECT * FROM booktable WHERE id = ?
			//1.欲搜尋的ID
			pstmt = conn.prepareStatement(SELECT_BY_ID);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				book = new Book(rs.getLong(1), rs.getString(2), rs.getString(3), rs.getTimestamp(4), rs.getString(5));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if( rs != null ) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		
		return book;
	}
	
	
	//寫入資料 (直接以Book型別寫入)
	public void insert(Book book) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			
			//SQL指令 INSERT INTO booktable VALUES (default,?,?,now(),?)
			//1.Title
			//2.Writer
			//3.ISBN
			//p.s.ID由SQL自動發派流水號  Time則由SQL指令產生現在時間
			pstmt = conn.prepareStatement(INSERT_FULL);
			pstmt.setString(1, book.getTitle());
			pstmt.setString(2, book.getWriter());
			pstmt.setString(3, book.getIsbn());
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//寫入資料(直接指派內容)
	public void insert(String title, String writer, String isbn) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			
			//SQL指令 INSERT INTO booktable VALUES (default,?,?,now(),?)
			//1.Title
			//2.Writer
			//3.ISBN
			//p.s.ID由SQL自動發派流水號  Time則由SQL指令產生現在時間
			pstmt = conn.prepareStatement(INSERT_FULL);
			pstmt.setString(1, title);
			pstmt.setString(2, writer);
			pstmt.setString(3, isbn);
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//寫入資料(批次指派內容)
	public int[] insert(List<Book> books) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);
			//SQL指令 INSERT INTO booktable VALUES (default,?,?,now(),?)
			//1.Title
			//2.Writer
			//3.ISBN
			//p.s.ID由SQL自動發派流水號  Time則由SQL指令產生現在時間
			pstmt = conn.prepareStatement(INSERT_FULL);
			for(Book book:books){
				pstmt.setString(1, book.getTitle());
				pstmt.setString(2, book.getWriter());
				pstmt.setString(3, book.getIsbn());
				pstmt.addBatch();
			}
			int[] i =  pstmt.executeBatch();
			conn.commit();
			return i;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	//更新目標資料
	public void update(Book book, String title) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			
			

			//SQL指令 UPDATE booktable SET title=?, writer=?, publication=now(),isbn=? WHERE title=?
			//根據輸入的資料項目，組合SQL語法(意即使用者不須被迫更新整列資料)
			//SQL指令組合開始
			StringBuilder sqlCommand = new StringBuilder();
			sqlCommand.append("UPDATE booktable SET ");
			int element = 0;
			boolean hasTitle = false;
			boolean hasWriter = false;
			boolean hasIsbn = false;
			if (book.getTitle()!=null) {
				hasTitle = true;
				sqlCommand.append("title=?");
				element++;
			}
			if (book.getWriter()!=null) {
				hasWriter = true;
				if (element>0) {
					sqlCommand.append(",writer=?");
				} else {
					sqlCommand.append("writer=?");
				}
				element++;
			}
			if (book.getIsbn()!=null) {
				hasIsbn = true;
				if (element>0) {
					sqlCommand.append(",isbn=?");
				} else {
					sqlCommand.append("isbn=?");
				}
				element++;
			}	
			sqlCommand.append(" WHERE title=?");
			//SQL指令組合完畢
			
			//當沒有任何一項資料需要更動時，直接結束本方法
			if (element<=0) {
				System.out.println("沒有輸入任何更新的資料");
				return;
			}
			
			//送出SQL指令，並且開始分配參數
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sqlCommand.toString());

			element = 0;
			if (hasTitle) {
				element++;
				pstmt.setString(element, book.getTitle());
			}
			if (hasWriter) {
				element++;
				pstmt.setString(element, book.getWriter());
			}
			if (hasIsbn) {
				element++;
				pstmt.setString(element, book.getIsbn());
			}
			pstmt.setString(element + 1, title);

			int result = pstmt.executeUpdate();
			System.out.println("成功更新"+result+"筆資料");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//DELETE目標資料
	public void deleteByAttribute(Book book) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {

			//SQL指令 DELETE FROM booktable WHERE title = ? and writer = ? and isbn = ?
			//根據輸入的項目來刪除資料
			StringBuilder sqlCommand = new StringBuilder();
			sqlCommand.append("DELETE FROM booktable WHERE ");
			int element = 0;
			boolean hasTitle = false;
			boolean hasWriter = false;
			boolean hasIsbn = false;
			if (book.getTitle()!=null) {
				hasTitle = true;
				sqlCommand.append("title=? ");
				element++;
			}
			if (book.getWriter()!=null) {
				hasWriter = true;
				if (element>0) {
					sqlCommand.append("and writer=? ");
				} else {
					sqlCommand.append("writer=? ");
				}
				element++;
			}
			if (book.getIsbn()!=null) {
				hasIsbn = true;
				if (element>0) {
					sqlCommand.append("and isbn=? ");
				} else {
					sqlCommand.append("isbn=? ");
				}
				element++;
			}	
			//SQL指令組合完畢
			
			//當沒有任何一項資料需要更動時，直接結束本方法
			if (element<=0) {
				System.out.println("沒有輸入任何更新的資料");
				return;
			}
			
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(sqlCommand.toString());
			//根據已指定的項目輸入參數
			element = 0;
			if (hasTitle) {
				element++;
				pstmt.setString(element, book.getTitle());
			}
			if (hasWriter) {
				element++;
				pstmt.setString(element, book.getWriter());
			}
			if (hasIsbn) {
				element++;
				pstmt.setString(element, book.getIsbn());
			}

			int result = pstmt.executeUpdate();
			System.out.println("成功刪除"+result+"筆資料");
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	//DELETE目標資料
		public void deleteAll() {
			Connection conn = null;
			PreparedStatement pstmt = null;
			try {

				//SQL指令 DELETE FROM booktable
				StringBuilder sqlCommand = new StringBuilder();
				sqlCommand.append("DELETE FROM booktable");
				conn = dataSource.getConnection();
				pstmt = conn.prepareStatement(sqlCommand.toString());
				int result = pstmt.executeUpdate();
				System.out.println("成功刪除"+result+"筆資料");
				
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {

				
				if( pstmt != null ) {
					try {
						pstmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				
				if( conn != null ) {
					try {
						conn.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	
	public void deleteByDate(String beginDate, String endDate){
		if (beginDate == null && endDate == null) {
			System.out.println("日期不能皆為空值");
			return;
		}
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			//SQL指令 DELETE_BY_DATE_TIME delete from booktable where publication > ? and publication < ?
			pstmt = conn.prepareStatement(DELETE_BY_DATE_TIME);
			
			//時間格式判定

			Timestamp beginDateTs = null;
			Timestamp endDateTs = null;
			try {
				if (beginDate == null) {
					beginDateTs = new Timestamp(0L);
				} else {
					beginDateTs = new Timestamp(java.sql.Date.valueOf(beginDate).getTime());
				}
				
				if (beginDate == null) {
					endDateTs = new Timestamp(0L);
				} else {
					endDateTs = new Timestamp(java.sql.Date.valueOf(endDate).getTime());
				}
				
			} catch (IllegalArgumentException e1) {
				System.out.println("日期格式錯誤");
				return;
			}
			
			pstmt.setTimestamp(1, beginDateTs);
			pstmt.setTimestamp(2, endDateTs);
			int result = pstmt.executeUpdate();
			System.out.println("成功刪除"+result+"筆資料");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {

			
			if( pstmt != null ) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			
			if( conn != null ) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}
